package com.dasart.JRMengine;

import java.io.BufferedInputStream;
import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.MalformedURLException;
import java.net.URI;
import java.net.URL;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class JRomManager {
	
	private Connection con;
	
	public boolean scanDirForRoms(String path, String console){
		
		
		File baseDir = new File(path);
		
		File[] files = baseDir.listFiles();
		String language;
		String name;
		String filePath;
		Matcher matcher;
		String sql;
		for (File file : files) {
			filePath = file.getPath().replace("'", "|");
			language = "";
			try {
				matcher = Pattern.compile("\\(.*\\)").matcher(filePath);
				matcher.find();
				language = matcher.group().replace("'", "|");
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			
			name = "";
			try {
				matcher = Pattern.compile(".*\\(").matcher(file.getName());
				matcher.find();
				name = matcher.group().substring(0, matcher.group().length() - 1).replace("'", "|");
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			
			sql = "INSERT INTO roms(name, language, console, path) VALUES ('"
															+name+"','"
															+language+"','"
															+console+"','"
															+filePath+"')";
			try {
				Statement stmt = con.createStatement();
				stmt.execute(sql);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
				
		return false;
	}
	

	public List<Integer> searchByName(String name){
		try {
			Statement stmt = con.createStatement();
			
			String sql = "SELECT id FROM roms WHERE name LIKE '%"+name+"%')";
			
			ResultSet result = stmt.executeQuery(sql);
			List<Integer> res = new ArrayList<Integer>();
			while (result.next()){
				res.add(result.getInt("name"));
			}
			
			return res;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	public List<Integer> searchByConsole(String console){
		try {
			Statement stmt = con.createStatement();
			
			String sql = "SELECT id FROM roms WHERE console LIKE '%"+console+"%'";
			
			ResultSet result = stmt.executeQuery(sql);
			List<Integer> res = new ArrayList<Integer>();
			while (result.next()){
				res.add(result.getInt("ID"));
			}
			
			return res;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public boolean initDB(){
		try {
			Class.forName("org.hsqldb.jdbcDriver");
		} catch (Exception e) {
			e.printStackTrace();
			// TODO: handle exception
		}
		
		try {
			boolean exists = (new File("database/JRMDB.script")).exists();
			if (exists) {
				con = DriverManager.getConnection("jdbc:hsqldb:file:database/JRMDB; shutdown=true","root", "");
			} else {
				con = DriverManager.getConnection("jdbc:hsqldb:file:database/JRMDB; shutdown=true","root", "");
				Statement stmt = con.createStatement();
				String sql;
				sql = "CREATE TABLE roms (" +
						"path VARCHAR_IGNORECASE(500)," +
						"name VARCHAR_IGNORECASE(200)," +
						"console VARCHAR_IGNORECASE(100)," +
						"language VARCHAR_IGNORECASE(100)," +
						"developer VARCHAR_IGNORECASE(100)," +
						"publisher VARCHAR_IGNORECASE(100)," +
						"langauge VARCHAR_IGNORECASE(100)," +
						"firstPublished VARCHAR_IGNORECASE(100)," +
						"platform VARCHAR_IGNORECASE(100)," +
						"genre VARCHAR_IGNORECASE(100)," +
						"gameModes VARCHAR_IGNORECASE(100)," +
						"story VARCHAR_IGNORECASE(1000)," +
						"pegi INTEGER," +
						"usk INTEGER," +
						"logopath VARCHAR_IGNORECASE(100)," +
						"id INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) PRIMARY KEY) SET IGNORECASE TRUE";
				stmt.execute(sql);
				sql = "CREATE TABLE consoles (" +
						"name VARCHAR_IGNORECASE(100)," +
						"origName VARCHAR_IGNORECASE(100)," +
						"romDirectoryPath VARCHAR_IGNORECASE(100)," +
						"manifacturer VARCHAR_IGNORECASE(100)," +
						"type VARCHAR_IGNORECASE(100)," +
						"publishedJP VARCHAR_IGNORECASE(100)," +
						"publishedAM VARCHAR_IGNORECASE(100)," +
						"publishedEU VARCHAR_IGNORECASE(100)," +
						"mainCpu VARCHAR_IGNORECASE(100)," +
						"mainGpu VARCHAR_IGNORECASE(100)," +
						"soldCopies INTEGER," +
						"generation VARCHAR_IGNORECASE(100)," +
						"mostPopularGame VARCHAR_IGNORECASE(100)," +
						"prevConsole VARCHAR_IGNORECASE(100)," +
						"nextConsole VARCHAR_IGNORECASE(100))  SET IGNORECASE TRUE";
				stmt.execute(sql);
			}

		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} 
		return true;
	}
	public boolean closeDB(){
		if (con != null) {
			try {
				con.close();
			} catch (Exception e2) {
				return false;
			}
		}
		return true;
	}
}
